﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Data.SqlClient;
using System.Linq;
using Library.OrmEnterpriseEntity.TableInfo;

namespace Library.OrmEnterpriseEntity.Factor {
	///<summary>
	///</summary>
	public class SqlServerContext : BaseDbContext {
		protected string WrapParameter(DbColumnView dbColumn) {
			return WrapParameter(dbColumn.ColumnName);
		}

		protected string WrapParameter(string parameter) {
			return "@" + parameter;
		}

		protected string WrapColumn(DbColumnView dbColumn) {
			return WrapColumn(dbColumn.ColumnName);
		}

		protected string WrapColumn(string columnName) {
			return "[" + columnName + "]";
		}


		//private class DateTimeParser : BaseTypeParser<DateTime> {
		//  public DateTimeParser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override DateTime Parse(string str) {
		//    if (Context.CustomDateParse != null) {
		//      var e = new CustomDateParseEventArgs(str);
		//      Context.CustomDateParse(Context, e);
		//      if (e.Success) return e.Result;
		//    }
		//    DateTime value;
		//    if (DateTime.TryParse(str, out value)) return value;
		//    return DateTimeColumn.DefaultDateTime;
		//  }
		//}

		//private class SingleParser : BaseTypeParser<Single> {
		//  public SingleParser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override Single Parse(string str) {
		//    Single value;
		//    if (Single.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class DoubleParser : BaseTypeParser<Double> {
		//  public DoubleParser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override Double Parse(string str) {
		//    Double value;
		//    if (Double.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class DecimalParser : BaseTypeParser<Decimal> {
		//  public DecimalParser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override Decimal Parse(string str) {
		//    Decimal value;
		//    if (Decimal.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class SByteParser : BaseTypeParser<SByte> {
		//  public SByteParser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override SByte Parse(string str) {
		//    SByte value;
		//    if (SByte.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class ByteParser : BaseTypeParser<byte> {
		//  public ByteParser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override byte Parse(string str) {
		//    byte value;
		//    if (byte.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class Int16Parser : BaseTypeParser<Int16> {
		//  public Int16Parser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override short Parse(string str) {
		//    short value;
		//    if (Int16.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class UInt16Parser : BaseTypeParser<UInt16> {
		//  public UInt16Parser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override UInt16 Parse(string str) {
		//    UInt16 value;
		//    if (UInt16.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class Int32Parser : BaseTypeParser<Int32> {
		//  public Int32Parser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override Int32 Parse(string str) {
		//    Int32 value;
		//    if (Int32.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class UInt32Parser : BaseTypeParser<UInt32> {
		//  public UInt32Parser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override UInt32 Parse(string str) {
		//    UInt32 value;
		//    if (UInt32.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class Int64Parser : BaseTypeParser<Int64> {
		//  public Int64Parser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override Int64 Parse(string str) {
		//    Int64 value;
		//    if (Int64.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}
		//private class UInt64Parser : BaseTypeParser<UInt64> {
		//  public UInt64Parser(QueryOperator qr, string data, string[] listData, SqlServerContext context)
		//    : base(qr, data, listData, context) {
		//  }

		//  protected override UInt64 Parse(string str) {
		//    UInt64 value;
		//    if (UInt64.TryParse(str, out value)) return value;
		//    return 0;
		//  }
		//}

		//private interface ITypeParser {
		//  bool Result { get; }
		//  object GetValue();
		//}
		//private abstract class BaseTypeParser<T> : ITypeParser {
		//  protected readonly SqlServerContext Context;
		//  protected BaseTypeParser(QueryOperator qr, string data, string[] listData, SqlServerContext context) {
		//    _qrOperator = qr;
		//    _data = data;
		//    _listData = listData;
		//    Context = context;
		//  }

		//  private readonly string _data;
		//  private readonly string[] _listData;
		//  private readonly QueryOperator _qrOperator;
		//  public bool Result { get; private set; }
		//  protected abstract T Parse(string str);
		//  public object GetValue() {
		//    //switch (_qrOperator) {
		//    //  case QueryOperator.Diff:
		//    //  case QueryOperator.Equal:
		//    //  case QueryOperator.SmallOrEqual:
		//    //  case QueryOperator.BigOrEqual:
		//    //    if (_data == null) {
		//    //      Result = false;
		//    //      return null;
		//    //    }
		//    //    Result = true;
		//    //    return Parse(_data);

		//    //  case QueryOperator.Between:
		//    //  case QueryOperator.In:
		//    //    if (_listData == null) {
		//    //      return new T[0];
		//    //    }
		//    //    var ts = new List<T>();
		//    //    for (int i = 0; i < _listData.Length; i++) {
		//    //      ts.Add(Parse(_listData[i]));
		//    //    }
		//    //    Result = true;
		//    //    return ts;
		//    //  default:
		//    //    Result = false;
		//    //    return null;
		//    //}
		//  }
		//}




		protected virtual string BuildOperator(QueryOperator queryOperator) {
			switch (queryOperator) {
				case QueryOperator.Big:
					return ">";
				case QueryOperator.BigOrEqual:
					return ">=";
				case QueryOperator.Equal:
					return "=";

				case QueryOperator.Small:
					return "<";
				case QueryOperator.SmallOrEqual:
					return "<=";
				case QueryOperator.Like:
					return "LIKE";
				case QueryOperator.In:
					return "IN";
				case QueryOperator.Xor:
					return "&";
				case QueryOperator.Diff:
					return "<>";
			}
			throw new NotSupportedException("Operator: " + queryOperator);
		}

		protected virtual string BuildOperator(QueryOperator queryOperator, QueryParameter parameter) {
			return BuildOperator(queryOperator);
		}

		protected virtual string BuildExpOperator(ExpressionOperator expressionOperator) {
			switch (expressionOperator) {
				case ExpressionOperator.Or:
					return "OR";
				case ExpressionOperator.And:
					return "AND";
			}
			throw new NotSupportedException("Operator: " + expressionOperator);
		}



		public override bool DoInsert(GlobalEntity dbTable) {
			bool hasColumn = false;
			var textBuilder = new StringBuilder();
			textBuilder.Append("INSERT INTO " + dbTable.TableName);
			textBuilder.Append("(");
			for (int i = 1; i < dbTable.Columns.Count; i++) {
				var column = dbTable.Columns[i];
				if (hasColumn)
					textBuilder.Append(",");
				hasColumn = true;
				textBuilder.Append(WrapColumn(column.ColumnName));

			}
			hasColumn = false;
			textBuilder.Append(") VALUES (");
			for (int i = 1; i < dbTable.Columns.Count; i++) {
				var column = dbTable.Columns[i];
				if (hasColumn) textBuilder.Append(",");
				hasColumn = true;
				textBuilder.Append(WrapParameter(column.ColumnName));
			}

			textBuilder.Append(") ;SELECT SCOPE_IDENTITY();");

			var insertText = textBuilder.ToString();


			var values = new List<object>();
			var parameter = new List<String>();
			var types = new List<DbType>();
			// because 0 index is RowId column
			for (int i = 1; i < dbTable.Columns.Count; i++) {
				DbColumn column = dbTable.Columns[i];
				values.Add(column.DbValue);
				parameter.Add(WrapParameter(column.ColumnName));
				types.Add(column.DbType);
			}
			dbTable.RowId.DbValue = ExcuteScalar(insertText, values.ToArray(), parameter.ToArray(), types.ToArray(), CommandType.Text);
			var trans = TransactionManager.GetCurrentTran();
			if (trans != null) {
				dbTable.State = EntityState.Inserting;
				trans.TranCommited += delegate {
					dbTable.Columns.AceptChange();
					dbTable.State = EntityState.DbNoChange;
				};
				trans.TranRollbacked += delegate {
					dbTable.State = EntityState.New;
				};
			} else {
				dbTable.Columns.AceptChange();
				dbTable.State = EntityState.DbNoChange;
			}

			return true;
		}

		public override bool DoUpdate(GlobalEntity dbTable) {
			var values = new List<object>();
			var parameter = new List<String>();
			var types = new List<DbType>();
			var textBuilder = new StringBuilder();
			textBuilder.Append("UPDATE " + dbTable.TableName);
			textBuilder.Append(" SET ");
			int changedColumn = 0;
			// sure that Sfuvc allway changed from DB in updated;
			bool hasCols = false;
			for (int i = 1; i < dbTable.Columns.Count; i++) {
				var column = dbTable.Columns[i];
				if (column.ValueDbChange) {
					changedColumn++;
					if (hasCols)
						textBuilder.Append(",");
					hasCols = true;

					textBuilder.Append(WrapColumn(column.ColumnName));
					textBuilder.Append(BuildOperator(QueryOperator.Equal));
					string p = String.Format("@P{0}", parameter.Count);
					textBuilder.Append(p);

					values.Add(column.DbValue);
					parameter.Add(p);
					types.Add(column.DbType);
				}
			}
			if (!hasCols || changedColumn == 1) {
				dbTable.State = EntityState.DbNoChange;
				return false;
			}

			string rowIdParameter = String.Format("@P{0}", parameter.Count);
			textBuilder.Append(" WHERE ");
			textBuilder.Append(WrapColumn(dbTable.RowId.ColumnName));
			textBuilder.Append(BuildOperator(QueryOperator.Equal));
			textBuilder.Append(rowIdParameter);

			values.Add(dbTable.RowId.DbValue);
			parameter.Add(rowIdParameter);
			types.Add(dbTable.RowId.DbType);

			textBuilder.Append(" AND ");
			var sfuvcParameter = String.Format("@P{0}", parameter.Count);
			textBuilder.Append(WrapColumn(dbTable.Sfuvc.ColumnName));
			textBuilder.Append(BuildOperator(QueryOperator.Equal));
			textBuilder.Append(sfuvcParameter);

			values.Add(dbTable.Sfuvc.Original);
			parameter.Add(sfuvcParameter);
			types.Add(dbTable.Sfuvc.DbType);

			if (dbTable.ColumnRowType != null) {
				textBuilder.Append(" AND ");
				textBuilder.Append(WrapColumn(dbTable.ColumnRowType.ColumnName));
				textBuilder.Append(BuildOperator(QueryOperator.Equal));
				textBuilder.Append(dbTable.ColumnRowType.Value);
			}

			textBuilder.Append("; SELECT @@ROWCOUNT");


			var updateCommand = textBuilder.ToString();
			var result = ExcuteScalar(updateCommand, values.ToArray(), parameter.ToArray(), types.ToArray(), CommandType.Text);
			if (Convert.ToInt32(result) <= 0) throw new UpdateNoEffectException(dbTable);
			var trans = TransactionManager.GetCurrentTran();
			if (trans != null) {
				dbTable.State = EntityState.Updating;

				trans.TranCommited += delegate {
					dbTable.Columns.AceptChange();
					dbTable.State = EntityState.DbNoChange;
				};
				trans.TranRollbacked += delegate {
					dbTable.State = EntityState.DbChange;
				};
			} else {
				dbTable.Columns.AceptChange();
				dbTable.State = EntityState.DbNoChange;
			}
			return true;
		}

		public override bool DoDelete(GlobalEntity entity) {
			var textBuilder = new StringBuilder();
			textBuilder.Append("DELETE FROM " + entity.TableName);
			textBuilder.Append(" WHERE ");
			textBuilder.Append(WrapColumn(entity.RowId.ColumnName));
			textBuilder.Append(BuildOperator(QueryOperator.Equal));
			textBuilder.Append(WrapParameter(entity.RowId.ColumnName));

			if (entity.ColumnRowType != null) {
				textBuilder.Append(" AND ");
				textBuilder.Append(WrapColumn(entity.ColumnRowType.ColumnName));
				textBuilder.Append(BuildOperator(QueryOperator.Equal));
				textBuilder.Append(entity.ColumnRowType.Value);
			}

			textBuilder.Append(" AND ");
			textBuilder.Append(WrapColumn(entity.Sfuvc.ColumnName));
			textBuilder.Append(BuildOperator(QueryOperator.Equal));
			textBuilder.Append(entity.Sfuvc.Value);
			textBuilder.Append("; SELECT @@ROWCOUNT");
			var deleteCommandText = textBuilder.ToString();
			var result = ExcuteScalar(deleteCommandText,
				new object[] { entity.RowId.Value },
				new[] { WrapParameter(entity.RowId.ColumnName) },
				new[] { entity.RowId.DbType }, CommandType.Text);

			if (Convert.ToInt32(result) <= 0) throw new UpdateNoEffectException(entity);
			var trans = TransactionManager.GetCurrentTran();
			if (trans != null) {
				var oldState = entity.State;
				entity.State = EntityState.Deleting;
				trans.TranRollbacked += delegate {
					entity.State = oldState;
				};
				trans.TranCommited += delegate {
					entity.MarkDelete = false;
					entity.State = EntityState.Delete;
				};
			} else {
				entity.MarkDelete = false;
				entity.State = EntityState.Delete;
			}
			return true;
		}

		public override string BuildSqlWildcard(string windowWildCard) {
			if (String.IsNullOrEmpty(windowWildCard)) return String.Empty;
			return windowWildCard.Replace('*', '%').Replace('?', '_');
		}

		public override DbConnection CreateConnection() {
			return new SqlConnection();
		}

		public override DbConnection CreateConnection(string connectionString) {
			return new SqlConnection(connectionString);
		}

		public override DbDataReader ExcuteStatement(SelectStatementWrapper selectStatement) {
			return ExcuteReader(BuildQuery(selectStatement), selectStatement.Parameters, CommandType.Text, CommandBehavior.SequentialAccess);
		}

		public override int ExcuteStatement(DeleteStatementWrapper statement) {
			var commandBuilder = new StringBuilder();
			commandBuilder.AppendFormat("DELETE FROM [{0}] FROM [{0}] {1}", statement.DeleteTable.TableName, statement.TableAlias(statement.DeleteTable));
			foreach (var joinStatement in statement.Joins) {
				commandBuilder.Append(BuildQuery(joinStatement));
			}
			string whereClause = BuildQuery(statement.WhereClause());
			if (whereClause.Length > 0)
				commandBuilder.Append(" WHERE " + whereClause);
			commandBuilder.Append("; SELECT @@ROWCOUNT");
			if (statement.Statement.SqlTracking != null) {
				BuilderTracking(commandBuilder, statement.Statement);
				//if (statement.Statement.SqlTracking.Length > 0)
				//  statement.Statement.SqlTracking.Append(";");
				//statement.Statement.SqlTracking.Append(commandBuilder);
				//foreach (var parameter in statement.Parameters) {
				//  statement.Statement.SqlTracking.AppendLine(String.Format("{0}: {1}", parameter.GetType(), parameter.Value));
				//}
			}
			return Convert.ToInt32(ExcuteScalar(commandBuilder.ToString(), statement.Parameters, CommandType.Text));
		}

		public override int ExcuteStatement(UpdateStatementWrapper statement) {
			if (statement.NewCoumnValues.Count == 0) return 0;
			var query = new StringBuilder();
			query.AppendFormat("UPDATE {0} ", statement.TableAlias(statement.UpdateTable));
			var first = statement.NewCoumnValues[0];
			query.AppendFormat(" SET {2}.[{0}] = {1}", first.Key.ColumnName,
												 FindParameterName(new QueryParameter(first.Key.DbType, first.Value), statement),
												 statement.TableAlias(statement.UpdateTable));
			for (int i = 1; i < statement.NewCoumnValues.Count; i++) {
				first = statement.NewCoumnValues[i];
				query.AppendFormat(", {2}.[{0}] = {1}", first.Key.ColumnName,
												 FindParameterName(new QueryParameter(first.Key.DbType, first.Value), statement),
												 statement.TableAlias(statement.UpdateTable));
			}
			query.AppendFormat(" FROM [{0}] {1}", statement.UpdateTable.TableName, statement.TableAlias(statement.UpdateTable));

			foreach (var joint in statement.Joins) {
				query.Append(BuildQuery(joint));
			}

			var where = BuildQuery(statement.WhereClause());
			if (where.Length > 0) {
				query.Append(" WHERE ");
				query.Append(where);
			}
			query.Append("; SELECT @@ROWCOUNT");
			if (statement.Statement.SqlTracking != null) {
				BuilderTracking(query, statement.Statement);
			}
			return Convert.ToInt32(ExcuteScalar(query.ToString(), statement.Parameters, CommandType.Text));
		}

		private static void BuilderTracking(StringBuilder query, QueryStatement statement) {
			if (statement.SqlTracking.Length > 0)
				statement.SqlTracking.Append(";");
			statement.SqlTracking.Append(query);
			foreach (var parameter in statement.Parameters) {
				var value = String.Empty;
				switch (parameter.Type) {
					case DbType.StringFixedLength:
					case DbType.String:
					case DbType.AnsiString:
					case DbType.AnsiStringFixedLength:
						if (parameter.Value == null) {
							//statement.Statement.SqlTracking.Replace(parameter.SqlName, "''");
							value = "''";
						} else {
							value = "'" + parameter.Value.ToString().Replace("'", "''") + "'";
						}
						break;
					case DbType.Byte:
					case DbType.SByte:
					case DbType.Int16:
					case DbType.UInt16:
					case DbType.Int32:
					case DbType.UInt32:
					case DbType.Int64:
					case DbType.UInt64:
						if (parameter.Value == null) value = "0";
						else value = Convert.ToString(parameter.Value);
						break;
					case DbType.Currency:
					case DbType.Decimal:
					case DbType.Double:
					case DbType.Single:
					case DbType.VarNumeric:
						if (parameter.Value == null) value = "0";
						else {
							value = Convert.ToString(parameter.Value, System.Globalization.CultureInfo.GetCultureInfo("en-us"));
						}
						break;
					case DbType.Date:
					case DbType.DateTime:
					case DbType.DateTime2:
					case DbType.DateTimeOffset:
					case DbType.Time:
						if (parameter.Value == null) value = "'1900-01-01'";

						else {
							value = Convert.ToDateTime(parameter.Value).ToString("yyyy-MM-dd:hh:mm:ss");
						}
						break;

				}
				if (value.Length > 0) {
					statement.SqlTracking.Replace(parameter.SqlName, value);
				}
			}
		}

		private static int ComparePrioritySort(DbColumnView first, DbColumnView second) {
			return GetPrioritySort(first.DbType) - GetPrioritySort(second.DbType);
		}

		private static int GetPrioritySort(DbType dbType) {
			switch (dbType) {
				case DbType.Boolean:
					return 1;
				case DbType.Byte:
				case DbType.SByte:
					return 2;
				case DbType.Int16:
				case DbType.UInt16:
					return 3;
				case DbType.Int32:
				case DbType.UInt32:
				case DbType.Date:
				case DbType.Time:
					return 4;
				case DbType.UInt64:
				case DbType.Int64:
				case DbType.Single:
				case DbType.DateTime:
				case DbType.DateTime2:
				case DbType.DateTimeOffset:
					return 5;
				case DbType.Double:
				case DbType.Decimal:
				case DbType.Currency:
					return 6;
				case DbType.AnsiString:
				case DbType.AnsiStringFixedLength:
					return 7;
				case DbType.String:
				case DbType.StringFixedLength:
					return 8;
				default:
					return 9;
			}
		}

		private string BuildQuery(SelectStatementWrapper selectStatement) {
			var selectedColumns = selectStatement.SelectedCols;
			if (selectedColumns.Length == 0) {
				throw new EnterpriseEntityException("No column at select query");
			}
			var hasGroupStatement = selectStatement.GroupBy != null && selectStatement.GroupBy.Length > 0;
			var hasPaging = selectStatement.Page > 0 && selectStatement.PageSize > 0;
			var query = new StringBuilder();

			query.Append("SELECT");
			if (selectStatement.RowReturn > 0) {
				query.Append(" TOP " + selectStatement.RowReturn);
			}
			int col = 0;
			bool hasColumn = false;
			DbColumnView pagingOrderColumn = null;//selectedColumns[0];
			foreach (var queryColumn in selectedColumns) {
				col++;
				if (hasColumn) query.Append(",");

				hasColumn = true;
				var isAgreeColumn = false;
				if (ReferenceEquals(selectStatement.CountColumn, queryColumn)) {
					query.AppendFormat(" COUNT({0}.{1}) AS {2}", selectStatement.TableAlias(queryColumn.TableView),
														 WrapColumn(queryColumn.ColumnName),
														 WrapColumn("C" + col));
					isAgreeColumn = true;
				} else {
					DbColumnView column = queryColumn;
					if (selectStatement.Sum != null && selectStatement.Sum.Any(s => ReferenceEquals(s, column))) {
						query.AppendFormat(" SUM({0}.{1}) AS {2}", selectStatement.TableAlias(queryColumn.TableView),
														 WrapColumn(queryColumn.ColumnName),
														 WrapColumn("C" + col));
						isAgreeColumn = true;
					} else {
						query.AppendFormat(" {0}.{1} AS {2}", selectStatement.TableAlias(queryColumn.TableView),
														 WrapColumn(queryColumn.ColumnName),
														 WrapColumn("C" + col));
					}
				}
				if (hasGroupStatement) {
					if (!isAgreeColumn) {
						if (pagingOrderColumn != null) {
							var priority = ComparePrioritySort(pagingOrderColumn, queryColumn);
							if (priority > 0) pagingOrderColumn = queryColumn;
						} else {
							pagingOrderColumn = queryColumn;
						}
					}
				} else {
					if (pagingOrderColumn == null)
						pagingOrderColumn = queryColumn;
					else {
						if (queryColumn.IsKey) {
							pagingOrderColumn = queryColumn;
						} else {
							if (!pagingOrderColumn.IsKey) {
								var priority = ComparePrioritySort(pagingOrderColumn, queryColumn);
								if (priority > 0) pagingOrderColumn = queryColumn;
							}
						}
					}
				}
			}
			bool appendOrderInWhere = true;
			if (hasPaging) {
				//if (selectStatement.GroupBy != null && selectStatement.GroupBy.Length > 0) {
				//  throw new NotSupportedException("Paging not support for grouping clause");
				//}
				appendOrderInWhere = false;
				var orderBuilder = new StringBuilder();
				var orders = selectStatement.Orders;
				if (orders.Length > 0) {
					if (orders.Length > 0) {
						orderBuilder.Append(BuildQuery(orders[0]));
					}
					for (int i = 1; i < orders.Length; i++) {
						orderBuilder.Append("," + BuildQuery(orders[i]));
					}
				} else {
					if (pagingOrderColumn == null) pagingOrderColumn = selectedColumns[0];
					orderBuilder.AppendFormat("{0}.{1}", selectStatement.TableAlias(pagingOrderColumn.TableView), WrapColumn(pagingOrderColumn));
				}
				query.AppendFormat(", ROW_NUMBER() OVER (ORDER BY {0}) AS [RowNumber], COUNT(*) OVER() AS [TotalRow]", orderBuilder);
			}

			query.AppendFormat(" FROM [{0}] {1}", selectStatement.RootTable.TableName, selectStatement.TableAlias(selectStatement.RootTable));
			if (selectStatement.SelectOption != null) {
				var sql2005 = selectStatement.SelectOption as SqlServer2005SelectOption;
				if (sql2005 != null) {
					if (sql2005.NoLock)
						query.Append(" WITH(NOLOCK)");
				}
			}
			foreach (var joint in selectStatement.Joins) {
				query.Append(BuildQuery(joint));
			}


			string whereClause = BuildQuery(selectStatement.WhereClause());
			if (whereClause.Length > 0)
				query.Append(" WHERE " + whereClause);

			if (selectStatement.GroupBy != null && selectStatement.GroupBy.Length > 0) {
				var group = selectStatement.GroupBy[0];
				query.AppendFormat(" GROUP BY {0}.{1}", selectStatement.TableAlias(group.TableView), WrapColumn(group.ColumnName));
				for (int z = 1; z < selectStatement.GroupBy.Length; z++) {
					group = selectStatement.GroupBy[z];
					query.AppendFormat(",{0}.{1}", selectStatement.TableAlias(group.TableView), WrapColumn(group.ColumnName));
				}
			}

			if (appendOrderInWhere) {
				var orders = selectStatement.Orders;

				if (orders.Length > 0) {
					query.Append(" ORDER BY ");
					query.Append(BuildQuery(orders[0]));
				}
				for (int i = 1; i < orders.Length; i++) {
					query.Append("," + BuildQuery(orders[i]));
				}
			}
			if (selectStatement.Page > 0 && selectStatement.PageSize > 0) {
				query.Insert(0, "SELECT * FROM (");
				query.AppendFormat(") AS [T] WHERE [RowNumber] > {0} AND [RowNumber] <= {1}", (selectStatement.Page - 1) * selectStatement.PageSize, (selectStatement.Page) * selectStatement.PageSize);
			}
			if (selectStatement.Statement.SqlTracking != null) {
				BuilderTracking(query, selectStatement.Statement);
				//if (selectStatement.Statement.SqlTracking.Length > 0)
				//  selectStatement.Statement.SqlTracking.Append(";");
				//selectStatement.Statement.SqlTracking.Append(query);
				//foreach (var parameter in selectStatement.Parameters) {
				//  selectStatement.Statement.SqlTracking.AppendLine(String.Format("{0}: {1}", parameter.GetType(), parameter.Value));
				//}
			}
			return query.ToString();
		}

		protected string BuildQuery(JoinStatement jointStatement) {
			DbColumnView first = null;
			if (jointStatement.Statement.RootRowType != null) first = jointStatement.Statement.RootRowType.ColumnRowType;
			var second = jointStatement.JoinTable.ColumnRowType;
			if (jointStatement.JoinExpression == null) {
				if (first != null && second != null && first != second) {
					jointStatement.JoinExpression = new QueryExpBuilder(first, QueryOperator.Equal, second);
					jointStatement.JoinExpression.Statement = jointStatement.Statement;
				}
			} else {
				if (first != null && second != null && first != second) {
					jointStatement.JoinExpression.Statement = jointStatement.Statement;
					jointStatement.JoinExpression = jointStatement.JoinExpression.Add(new QueryExpBuilder(first, QueryOperator.Equal, second), ExpressionOperator.And);
				}
			}
			string joinCondition = jointStatement.JoinExpression != null
															? " ON (" + BuildQuery(jointStatement.JoinExpression) + ")"
															: String.Empty;
			string join;
			switch (jointStatement.JoinType) {
				case JoinType.LeftJoin:
					join = " LEFT JOIN";
					break;
				case JoinType.RightJoin:
					join = " RIGHT JOIN";
					break;
				default:
					join = " JOIN";
					break;
			}
			return (join + " [" +
		jointStatement.JoinTable.TableName +
				"] " +
				jointStatement.Statement.TableAlias(jointStatement.JoinTable) + joinCondition);
		}

		protected string BuildQuery(OrderStatement orderStatement) {
			return String.Format("{0}.{1} {2}",
			orderStatement.Statement.TableAlias(orderStatement.Column.TableView),
				WrapColumn(orderStatement.Column.ColumnName),
				orderStatement.Order);
		}

		protected string BuildQuery(QueryWrapBuilder wrapBuilder) {
			return String.Format(" {0} {1}", BuildExpOperator(wrapBuilder.Operator), BuildQuery(wrapBuilder.Wrap));
		}

		protected string BuildQuery(QueryColumnBuilder queryColumnBuilder) {
			if (queryColumnBuilder.Operator == QueryOperator.Xor) {
				return String.Format("({0}.{1} {2} {3}.{4}) = {0}.{1}",
				queryColumnBuilder.Statement.TableAlias(queryColumnBuilder.First.TableView),
				WrapColumn(queryColumnBuilder.First.ColumnName),
				BuildOperator(queryColumnBuilder.Operator),
				queryColumnBuilder.Statement.TableAlias(queryColumnBuilder.Second.TableView),
				WrapColumn(queryColumnBuilder.Second.ColumnName));
			}
			return String.Format("{0}.{1} {2} {3}.{4}",
				queryColumnBuilder.Statement.TableAlias(queryColumnBuilder.First.TableView),
				WrapColumn(queryColumnBuilder.First.ColumnName),
				BuildOperator(queryColumnBuilder.Operator),
				queryColumnBuilder.Statement.TableAlias(queryColumnBuilder.Second.TableView),
				WrapColumn(queryColumnBuilder.Second.ColumnName));
		}
		protected string BuildQuery(ExpressionBuilder expressionBuilder) {
			switch (expressionBuilder.ExpType) {
				case ExpressionBuilder.ExpressionType.Wrap:
					return BuildQuery((QueryWrapBuilder)expressionBuilder);
				case ExpressionBuilder.ExpressionType.Parm:
					return BuildQuery((QueryParmBuilder)expressionBuilder);
				case ExpressionBuilder.ExpressionType.Exp:
					return BuildQuery((QueryExpBuilder)expressionBuilder);
				case ExpressionBuilder.ExpressionType.Column:
					return BuildQuery((QueryColumnBuilder)expressionBuilder);
				case ExpressionBuilder.ExpressionType.Mass:
					return BuildQuery((QueryMassBuilder)expressionBuilder);
			}

			return String.Empty;
		}

		protected string BuildQuery(QueryMassBuilder massBuilder) {
			var query = new StringBuilder();
			string[] arrayString = null;
			var filterExp = massBuilder.Filter;
			if (String.IsNullOrEmpty(filterExp)) throw new ArgumentException("Filter is null or emply in MassQuery");
			var expressions = filterExp.Split(',');
			foreach (var expression in expressions) {
				if (!String.IsNullOrEmpty(expression)) {
					var exp = expression.Trim();
					bool includeNot = false;
					if (exp.StartsWith("!")) {
						includeNot = true;
						exp = exp.Substring(1);
					}

					arrayString = exp.Split(new[] { ".." }, StringSplitOptions.None);
					if (arrayString.Length == 2) {
						var from = arrayString[0].Trim();
						var to = arrayString[1].Trim();
						if (from.Length == 0 && to.Length == 0) {
							// equal .. filter
							var value = ParseValueFromText(massBuilder, "..");
							var p = FindParameterName(new QueryParameter(massBuilder.Column.DbType, value), massBuilder.Statement);
							var qrOperator = includeNot ? QueryOperator.Diff : QueryOperator.Equal;
							if (query.Length > 0) {
								query.Append(includeNot ? " AND " : " OR ");
							}
							query.AppendFormat("({0}.[{1}] {2} {3})",
																 massBuilder.Statement.TableAlias(massBuilder.Column.TableView),
																 massBuilder.Column.ColumnName,
																 BuildOperator(qrOperator),
																 p);

						} else if (from.Length == 0) {
							var value = ParseValueFromText(massBuilder, to);
							var p = FindParameterName(new QueryParameter(massBuilder.Column.DbType, value), massBuilder.Statement);
							var qrOperator = includeNot ? QueryOperator.Big : QueryOperator.SmallOrEqual;
							if (query.Length > 0) query.Append(includeNot ? " AND " : " OR ");
							query.AppendFormat("({0}.[{1}] {2} {3})",
																 massBuilder.Statement.TableAlias(massBuilder.Column.TableView),
																 massBuilder.Column.ColumnName,
																 BuildOperator(qrOperator),
																 p);
						} else if (to.Length == 0) {
							var value = ParseValueFromText(massBuilder, from);
							var p = FindParameterName(new QueryParameter(massBuilder.Column.DbType, value), massBuilder.Statement);
							var qrOperator = includeNot ? QueryOperator.SmallOrEqual : QueryOperator.Big;
							if (query.Length > 0) query.Append(includeNot ? " AND " : " OR ");
							query.AppendFormat("({0}.[{1}] {2} {3})",
																 massBuilder.Statement.TableAlias(massBuilder.Column.TableView),
																 massBuilder.Column.ColumnName,
																 BuildOperator(qrOperator),
																 p);
						} else {// between
							var fromValue = ParseValueFromText(massBuilder, from);
							var pFrom = FindParameterName(new QueryParameter(massBuilder.Column.DbType, fromValue), massBuilder.Statement);
							var toValue = ParseValueFromText(massBuilder, to);
							var pTo = FindParameterName(new QueryParameter(massBuilder.Column.DbType, toValue), massBuilder.Statement);
							if (query.Length > 0) query.Append(includeNot ? " AND " : " OR ");
							if (includeNot) {
								query.AppendFormat("({0}.[{1}] {2} {3}) OR ({0}.[{1}] {4} {5})",
																 massBuilder.Statement.TableAlias(massBuilder.Column.TableView),
																 massBuilder.Column.ColumnName,
																 BuildOperator(QueryOperator.Small),
																 pFrom,
																 BuildOperator(QueryOperator.Big),
																 pTo);
							} else {
								query.AppendFormat("({0}.[{1}] >= {2} AND {0}.[{1}] <= {3})",
																 massBuilder.Statement.TableAlias(massBuilder.Column.TableView),
																 massBuilder.Column.ColumnName,
																 pFrom,
																 pTo);
							}
						}
					} else {// equal filter
						var value = ParseValueFromText(massBuilder, exp);
						//
						if (exp.Equals(value)) { // string db type
							var wildcard = BuildSqlWildcard(exp);
							var p = FindParameterName(new QueryParameter(massBuilder.Column.DbType, wildcard), massBuilder.Statement);
							if (wildcard == exp) {// not include wildcard
								var qrOperator = includeNot ? QueryOperator.Diff : QueryOperator.Equal;
								if (query.Length > 0) query.Append(includeNot ? " AND " : " OR ");
								query.AppendFormat("({0}.[{1}] {2} {3})",
																	 massBuilder.Statement.TableAlias(massBuilder.Column.TableView),
																	 massBuilder.Column.ColumnName,
																	 BuildOperator(qrOperator),
																	 p);
							} else {
								if (query.Length > 0) query.Append(includeNot ? " AND " : " OR ");
								if (includeNot) {
									query.AppendFormat("({0}.[{1}] NOT {2} {3})",
																	 massBuilder.Statement.TableAlias(massBuilder.Column.TableView),
																	 massBuilder.Column.ColumnName,
																	 BuildOperator(QueryOperator.Like),
																	 p);
								} else {
									query.AppendFormat("({0}.[{1}] {2} {3})",
																	 massBuilder.Statement.TableAlias(massBuilder.Column.TableView),
																	 massBuilder.Column.ColumnName,
																	 BuildOperator(QueryOperator.Like),
																	 p);
								}
							}

						} else {
							var p = FindParameterName(new QueryParameter(massBuilder.Column.DbType, value), massBuilder.Statement);
							var qrOperator = includeNot ? QueryOperator.Diff : QueryOperator.Equal;
							if (query.Length > 0) query.Append(includeNot ? " AND " : " OR ");
							query.AppendFormat("({0}.[{1}] {2} {3})",
																 massBuilder.Statement.TableAlias(massBuilder.Column.TableView),
																 massBuilder.Column.ColumnName,
																 BuildOperator(qrOperator),
																 p);
						}
					}
				}
			}
			if (query.Length > 0) {
				return "(" + query + ")";
			}
			return String.Empty;
		}

		private object ParseValueFromText(QueryMassBuilder massBuilder, string value) {
			if (massBuilder.CustomValueParse != null) {
				var customEventArgs = new CustomValueParseEventArgs(value, massBuilder.Column);
				massBuilder.CustomValueParse(this, customEventArgs);
				if (customEventArgs.Handled) return customEventArgs.Value;
			}
			if (String.IsNullOrEmpty(value)) return null;
			try {
				switch (massBuilder.Column.DbType) {
					case DbType.StringFixedLength:
					case DbType.String:
					case DbType.AnsiString:
					case DbType.AnsiStringFixedLength:
						return value;

					case DbType.Int64:
						return Int64.Parse(value);
					case DbType.UInt64:
						return UInt64.Parse(value);
					case DbType.Int32:
						return Int32.Parse(value);
					case DbType.UInt32:
						return UInt32.Parse(value);
					case DbType.Int16:
						return Int16.Parse(value);
					case DbType.UInt16:
						return UInt16.Parse(value);
					case DbType.Byte:
						return Byte.Parse(value);
					case DbType.SByte:
						return SByte.Parse(value);
					case DbType.Currency:
					case DbType.Decimal:
						return Decimal.Parse(value);
					case DbType.Double:
						return Double.Parse(value);
					case DbType.Single:
						return Single.Parse(value);

					case DbType.Date:
					case DbType.DateTime:
					case DbType.DateTime2:
					case DbType.DateTimeOffset:
						return DateTime.Parse(value);
				}
			} catch (FormatException) {
				return null;
			} catch (OverflowException) {
				return null;
			}
			return value;
		}
		protected string BuildQuery(QueryExpWrapBuilder wrapBuilder) {
			var builder = new StringBuilder();


			builder.Append("((");
			builder.Append(BuildQuery(wrapBuilder.First));
			builder.Append(") ");
			builder.Append(BuildExpOperator(wrapBuilder.Operator));
			builder.Append(" (");
			builder.Append(BuildQuery(wrapBuilder.Second));
			builder.Append(")) ");
			foreach (var expressionBuilder in wrapBuilder.Builders) {
				builder.Append(BuildQuery(expressionBuilder));
			}
			return builder.ToString();
		}


		protected string BuildQuery(QueryExpBuilder queryExpression) {
			var wrap = queryExpression as QueryExpWrapBuilder;
			if (wrap != null) {
				return BuildQuery(wrap);
			}
			var stringBuilder = new StringBuilder();
			foreach (var expressionBuilder in queryExpression.Builders) {
				stringBuilder.Append(BuildQuery(expressionBuilder));
			}

			return stringBuilder.ToString();
		}

		protected void BuildQuery(QueryExpWrapBuilder queryExpressionBuilder, StringBuilder firstBuilder) {
			firstBuilder.Insert(0, "(");
			firstBuilder.Append(") ");
			firstBuilder.Append(BuildExpOperator(queryExpressionBuilder.Operator));
			firstBuilder.AppendFormat(" ({0})", BuildQuery(queryExpressionBuilder.Second));
		}

		protected string FindParameterName(QueryParameter qParameter, StatementWrapper statement) {
			string parameterName = null;
			foreach (var parameter in statement.Parameters) {
				if (parameter.Type == qParameter.Type) {
					if (parameter.Value == null && qParameter.Value == null) {
						parameterName = parameter.SqlName;
						break;
					}
					if (parameter.Value != null && parameter.Value.Equals(qParameter.Value)) {
						parameterName = parameter.SqlName;
						break;
					}
				}
			}
			if (parameterName == null) {
				qParameter.SqlName = WrapParameter("P" + statement.Parameters.Count);
				parameterName = qParameter.SqlName;
				statement.Parameters.Add(qParameter);
			}
			return parameterName;
		}
		protected string FindParameterName(QueryParameter qParameter, QueryStatement statement) {
			string parameterName = null;
			foreach (var parameter in statement.Parameters) {
				if (parameter.Type == qParameter.Type) {
					if (parameter.Value == null && qParameter.Value == null) {
						parameterName = parameter.SqlName;
						break;
					}
					if (parameter.Value != null && parameter.Value.Equals(qParameter.Value)) {
						parameterName = parameter.SqlName;
						break;
					}
				}
			}
			if (parameterName == null) {
				qParameter.SqlName = WrapParameter("P" + statement.Parameters.Count);
				parameterName = qParameter.SqlName;
				statement.Parameters.Add(qParameter);
			}
			return parameterName;
		}

		protected string BuildQuery(QueryParmBuilder queryParameterBuilder) {
			string parameterName = null;
			if (queryParameterBuilder.Operator == QueryOperator.Xor) {
				parameterName = FindParameterName(queryParameterBuilder.Parameter, queryParameterBuilder.Statement);
				return String.Format("({0}.{1} {2} {3}) = {0}.{1}",
												queryParameterBuilder.Statement.TableAlias(queryParameterBuilder.Column.TableView),
												WrapColumn(queryParameterBuilder.Column.ColumnName),
												BuildOperator(queryParameterBuilder.Operator),
												parameterName);
			}

			if (queryParameterBuilder.Operator == QueryOperator.Between) {
				var enums = queryParameterBuilder.Parameter.Value as IEnumerable;
				object first = null;
				object second = null;
				bool isFirst = true;
				if (enums != null) {
					foreach (var en in enums) {
						if (isFirst) {
							isFirst = false;
							first = en;
						} else {
							second = en;
						}
					}

				}

				var firstParameter = FindParameterName(new QueryParameter(queryParameterBuilder.Parameter.Type, first),
																							 queryParameterBuilder.Statement);
				var secondParameter = FindParameterName(new QueryParameter(queryParameterBuilder.Parameter.Type, second),
																							 queryParameterBuilder.Statement);
				return String.Format("({0}.{1} >= {2} AND {0}.{1} <= {3})",
												queryParameterBuilder.Statement.TableAlias(queryParameterBuilder.Column.TableView),
												WrapColumn(queryParameterBuilder.Column.ColumnName),
												firstParameter,
												secondParameter);
			}

			if (queryParameterBuilder.Operator == QueryOperator.In) {
				var inBuilder = new StringBuilder();

				var first = true;
				var enums = queryParameterBuilder.Parameter.Value as IEnumerable;
				if (enums != null) {
					inBuilder.Append("(");
					foreach (var v in enums) {
						var p = new QueryParameter(queryParameterBuilder.Parameter.Type, v);
						var parameter = FindParameterName(p, queryParameterBuilder.Statement);
						if (first) {
							first = false;
							inBuilder.Append(parameter);
						} else {
							inBuilder.Append("," + parameter);
						}
					}
					if (first) {
						throw new EnterpriseEntityException("No item found in operator IN");
					}

					inBuilder.Append(")");
					parameterName = inBuilder.ToString();
				}
			}

			if (parameterName == null)
				parameterName = FindParameterName(queryParameterBuilder.Parameter, queryParameterBuilder.Statement);
			return String.Format("{0}.{1} {2} {3}",
													queryParameterBuilder.Statement.TableAlias(queryParameterBuilder.Column.TableView),
													WrapColumn(queryParameterBuilder.Column.ColumnName),
													BuildOperator(queryParameterBuilder.Operator),
													parameterName);
		}

	}
}
